﻿#include "../include/sql.h"
#include "include/global.h"
//#include <exception>
/*!
 * \brief SQL::setDb 配置数据库信息
 * \param db_name 数据库名
 * \param user 用户
 * \param pw 密码
 */
void SQL::setDb(const QString _dbname, const QString _user, const QString _passwd)
{
    dbname = _dbname;
    user = _user;
    passwd = _passwd;

    db.setDatabaseName(_dbname);
    db.setHostName("localhost");
    db.setUserName(_user);
    db.setPassword(_passwd);
}
/*!
 * \brief SQL::openDb 打开数据库连接
 * \return 连接状态
 */
bool SQL::openDb()
{
	//isOpened = false;
	//try
	{
		if(db.open())
			isOpened = true;
		else
			isOpened = false;
	}
	//catch (std::exception &e)
	{
	}

    return isOpened;
}
/*!
 * \brief SQL::changeDb 更改数据库
 * \param db_name
 * \return
 */
bool SQL::changeDb(const QString _dbname)
{
	if(!isOpened){
		return false;
	}
    QSqlQuery query;
    dbname = _dbname;
    QString s = "use  "+ _dbname;
    return query.exec(s);
}
/*!
 * \brief SQL::createDb 创建一个数据库,如果该数据库已经存在，则忽略
 * \param db_name
 * \return
 */
bool SQL::createDb(QString _dbname)
{
	if(!isOpened){
		return false;
	}
    QSqlQuery query;
    dbname = _dbname;
    QString s = "Create Database If Not Exists  " + _dbname;
    return query.exec(s);
}

bool SQL::creatTable(){
	if(!isOpened){
		return false;
	}
	createDataTable(SQL_G);
	createDataTable(SQL_Y);
	createDataTable(SQL_V);
	createSensorTable(SENSOR_TABLE);
	createStationTable(STATIONS_TABLE);
	return true;
}
/*!
 * \brief SQL::createTable  创建表，列为 id + 时间time + 数据data
 * \param table_
 * \return
 */
bool SQL::createDataTable(const QString &_table)
{
    QSqlQuery query;//foreign key(cid) references company(cid)
    table = _table;
    QString s = "CREATE TABLE   If Not Exists "+ table+ "(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, time timestamp, stationNo TINYINT , sensorNo TINYINT ,data float)";
    return query.exec(s);
}
bool SQL::createSensorTable(const QString &_table)
{
	QSqlQuery query;
	//table = _table;
	QString s = "CREATE TABLE   If Not Exists "+ _table+ "(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, sensorType TINYINT ,note char(50) )";
	return query.exec(s);
}
bool SQL::createStationTable(const QString &_table)
{
	QSqlQuery query;
	//table = _table;
	QString s = "CREATE TABLE   If Not Exists "+ _table+ "(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, stationNo TINYINT,sensorNum TINYINT,tel  char(50))";
	return query.exec(s);
}

bool SQL::execSql(QString &s){
	if(!isOpened){
		return false;
	}
	QSqlQuery query;
	query.exec(s);
	//qDebug()<<"Record "<<query.size()<<endl;
	int r = query.size();
	if(r == 0 || r == -1){
		return false;	
	}
	return true;
}
/*!
 * \brief SQL::insertData 向表中插入数据
 * \param table_
 * \param intime
 * \param t
 * \param d
 * \return
 */
bool SQL::insertData(const QString &_table, const QString &intime, uint t,int stationNo, int sensorNo, float d)
{
	if(!isOpened){
		return false;
	}
    QSqlQuery query;
    QString s = "INSERT INTO " + _table+"(time,stationNo, sensorNo,data) VALUES (:time, :stationNo, :sensorNo, :data)";
    query.prepare(s);
    //query.bindValue(":id", t);
    query.bindValue(":time", intime);
	query.bindValue(":stationNo", stationNo);
	query.bindValue(":sensorNo", sensorNo);
    query.bindValue(":data", d);
    return query.exec();
}
bool SQL::insertSensorType(const QString &_table, int id, int type, QString &note){
	if(!isOpened){
		return false;
	}
	QSqlQuery query;
	QString s = "INSERT INTO " + _table+"(sensorType, note) VALUES (:sensorType, :note)";
	query.prepare(s);
	//query.bindValue(":id", id);
	query.bindValue(":sensorType", type);
	query.bindValue(":note", note);
	return query.exec();
}
bool SQL::insertStations(const QString &_table, int id, int no, int sensorNums, QString &tel){
	if(!isOpened){
		return false;
	}
	QSqlQuery query;
	QString s = "INSERT INTO " + _table+"(stationNo, sensorNum,tel) VALUES (:stationNo, :sensorNum, :tel)";
	query.prepare(s);
	//query.bindValue(":id", id);
	query.bindValue(":stationNo", no);
	query.bindValue(":sensorNum", sensorNums);
	query.bindValue(":tel", tel);
	return query.exec();
}
/*!
 * \brief SQL::selectTable 查询数据，并以vector返回
 * \param table
 * \param cnt
 * \return
 */
#if 0
MyVector& SQL::selectTable(QString table, int& cnt)
{
    QSqlQuery query;
    QString s = "SELECT id, time, data FROM " + table;
    query.exec(s);
    vec.clear();
    while (query.next())
    {
        vec.push_back(QPointF(query.value(0).toInt(),query.value(2).toFloat()));
    }
    cnt = vec.size();
    return vec;
}
MyVector& SQL::getResult()
{
    return vec;
}
#endif
